Mega Code Archive
Date Time Functions
1) =DATE(2001, 15, 2) returns March 2, 2002, because March 2002 is 15 months after the first month of 2001
2) =DATE(2003, 2, 30) returns March 2, 2003, because February does not have 30 days
3) =DATE(2006, -5, 16) returns the date July 16, 2005 because July 2005 is 5 months before the first month of 2006
4) =EDATE(01152003,-1) returns the date, one month before
5) =EOMONTH(01012008,-3) returns the date of the last day of the month, three months before
6) =HOUR(15
7) =MONTH(TODAY())
8) =NETWORKDAYS(A2,A3,A4
9) =NOW() returns the current date and time
10) =SECOND(4
11) =TIMEVALUE(22-Aug-2008 6
12) =TODAY() returns the serial number of todays date
13) =WEEKDAY(2142008,2)
14) =YEAR(7510)
15) Add two hours and 30 minutes
16) Calculates the number of years between two dates
17) Calculating the Difference between Two Times
18) Calculating the Number of Days between Two Dates
19) CHRISTMAS DAY always falls on December 25
20) Counting the days until your birthday
21) Counting your age, in days
22) DATE(year,month,day) returns a particular date
23) DATEDIF(Start date, End date, Interval)
24) Determining the Last Day of a Month
25) Determining Whether a Year Is a Leap Year
26) Display the current time as a time serial number
27) Display the time, combined with text
28) Displaying Any Time
29) EDATE(start_date,months) returns the date that is the indicated number of months before or after the start date
30) EOMONTH(start_date,months) returns the last day of the month before or after a specified number of months
31) Expresses the date in cell A1 as an ordinal date
32) Generate a series of dates separated by one month
33) Get the day of the first day of the year 2007
34) HOUR() converts to an hour
35) Import data that contains dates coded as text strings
36) INDEPENDENCE DAY always falls on July 4
37) MINUTE(serial_number) converts to a minute
38) MONTH(serial_number) converts a serial number to a month
39) NETWORKDAYS(start_date,end_date,holidays) returns the number of whole workdays between two dates
40) Return the age of the person whose date of birth you enter into cell A1
41) Return the date for Independence Day (July 4th) of the current year
42) Return the day of the year for a date stored in cell A1
43) Return the number of days remaining in the year from a particular date
44) Returns an integer between 1 and 4 that corresponds to the calendar quarter for the date
45) Returns the number of days in a month
46) Round the time in cell A1 to the nearest 15 minutes (quarter of an hour)
47) Round the time in cell A1 to the nearest hour
48) Round the time in cell A1 to the nearest minute
49) SECOND(serial_number) converts to a second
50) The DATEVALUE function converts a text string that looks like a date into a date serial number
51) TIME(hour,minute,second) returns the serial number of a particular time
52) TIMEVALUE(time_text) converts a time in the text to a serial number
53) To enter New Years Day for a specific year in cell A1
54) To generate a series of dates separated by one year
55) To generate a series of dates separated by seven days
56) To round a time to the nearest 30 minutes, change 0 25 to 0 5
57) Use the DATEDIF function to calculate an age
58) Use the WORKDAY function to determine the date ten working days from January 8, 2007
59) Use the YEARFRAC function to calculate the number of years between two dates
60) VETERANS DAY always falls on November 11
61) WEEKDAY(serial_number,return_type) converts a serial number to a day of the week
62) WEEKNUM(serial_num,return_type) converts a number to week number
63) YEAR() returns the year